using System;
using nwind;
using Gtk;
using Mono.Data.Sqlite;
using System.Linq;

namespace BanVeTau
{
	public partial class NewTicket : Gtk.Window
	{
		nwind.Main db;
		public Gtk.ListStore ticket_store;
		public Gtk.ListStore schedule_store;

		public NewTicket () : 
    base(Gtk.WindowType.Toplevel)
		{
			var conn = new SqliteConnection (
				       "DbLinqProvider=Sqlite;" +
				"Data Source=db_bvt.db"
			);
			db = new nwind.Main (conn);
      
			this.Build ();




			ticket_store = new Gtk.ListStore (typeof(Ticket));
			schedule_store = new Gtk.ListStore (typeof(Schedule));


			lb_form_name.LabelProp = "<span size=\"x-large\" weight=\"bold\">Create New Ticket</span>";
			lb_bus_id.LabelProp = "<span weight=\"bold\">Schedule ID</span>";
			lb_cus_idcard.LabelProp = "<span weight=\"bold\">Customer ID</span>";
			lb_cus_name.LabelProp = "<span weight=\"bold\">Customer Name</span>";
			lb_phone.LabelProp = "<span weight=\"bold\">Customer Phone</span>";
			lb_res_seat.LabelProp = "<span weight=\"bold\">Reserved Seats</span>";
			lb_total.LabelProp = "<span weight=\"bold\">Total</span>";

			fr_lb_available_bus.LabelProp = "<span weight=\"bold\">Available Bus</span>";
			fr_lb_ticket_registered.LabelProp = "<span weight=\"bold\">Registered Tickets</span>";

			btn_create.Label = "Create";
			btn_delete.Label = "Delete";
			btn_update.Label = "Update";
			btn_load_db.Label = "Load Database";

      
			/* columns of available bus_id */
			Gtk.TreeViewColumn sche_id_col = new Gtk.TreeViewColumn ();
			sche_id_col.Title = "Schedule ID";
			Gtk.CellRendererText sche_id_cell = new Gtk.CellRendererText ();
			sche_id_col.PackStart (sche_id_cell, true);
     
			sche_id_col.SetCellDataFunc (sche_id_cell, new Gtk.TreeCellDataFunc (RenderScheId));


			/* date_time column of trv_article_list */
			Gtk.TreeViewColumn sche_datetime_col = new Gtk.TreeViewColumn ();
			sche_datetime_col.Title = "Date Time";
			Gtk.CellRendererText sche_datetime_cell = new Gtk.CellRendererText ();
			sche_datetime_col.PackStart (sche_datetime_cell, true);
			sche_datetime_col.SetCellDataFunc (sche_datetime_cell, new Gtk.TreeCellDataFunc (RenderScheDatetime));

			/* start_loc of trv_article_list */
			Gtk.TreeViewColumn sche_startloc_col = new Gtk.TreeViewColumn ();
			sche_startloc_col.Title = "Start Location";
			Gtk.CellRendererText sche_startloc_cell = new Gtk.CellRendererText ();
			sche_startloc_col.PackStart (sche_startloc_cell, true);
			sche_startloc_col.SetCellDataFunc (sche_startloc_cell, new Gtk.TreeCellDataFunc (RenderScheStartloc));

			/* stop_loc column of trv_small_stop */
			Gtk.TreeViewColumn sche_stoploc_col = new Gtk.TreeViewColumn ();
			sche_stoploc_col.Title = "Stop Location";
			Gtk.CellRendererText sche_stoploc_cell = new Gtk.CellRendererText ();
			sche_stoploc_col.PackStart (sche_stoploc_cell, true);
			sche_stoploc_col.SetCellDataFunc (sche_stoploc_cell, new Gtk.TreeCellDataFunc (RenderScheStoploc));

			/* time_consume column of trv_small_stop */
			Gtk.TreeViewColumn sche_timeconsume_col = new Gtk.TreeViewColumn ();
			sche_timeconsume_col.Title = "Time";
			Gtk.CellRendererText sche_timeconsume_cell = new Gtk.CellRendererText ();
			sche_timeconsume_col.PackStart (sche_timeconsume_cell, true);
			sche_timeconsume_col.SetCellDataFunc (sche_timeconsume_cell, new Gtk.TreeCellDataFunc (RenderScheTimeconsume));

			Gtk.TreeViewColumn sche_seat_col = new Gtk.TreeViewColumn ();
			sche_seat_col.Title = "Seats";
			Gtk.CellRendererText sche_seat_cell = new Gtk.CellRendererText ();
			sche_seat_col.PackStart (sche_seat_cell, true);
			sche_seat_col.SetCellDataFunc (sche_seat_cell, new Gtk.TreeCellDataFunc (RenderScheSeat));
      
      


			trv_bus_avail.AppendColumn (sche_id_col);
			trv_bus_avail.AppendColumn (sche_datetime_col);
			trv_bus_avail.AppendColumn (sche_startloc_col);
			trv_bus_avail.AppendColumn (sche_stoploc_col);
			trv_bus_avail.AppendColumn (sche_timeconsume_col);
			trv_bus_avail.AppendColumn (sche_seat_col);
			trv_bus_avail.Model = schedule_store;
			trv_bus_avail.CursorChanged += OnBusAvailChanged;

			/************************************************************/
			/* No column of trv_ticket_registered */
			Gtk.TreeViewColumn tsche_id_col = new Gtk.TreeViewColumn ();
			tsche_id_col.Title = "Schedule ID";
			Gtk.CellRendererText tsche_id_cell = new Gtk.CellRendererText ();
			tsche_id_col.PackStart (tsche_id_cell, true);
			tsche_id_col.SetCellDataFunc (tsche_id_cell, new Gtk.TreeCellDataFunc (RenderTScheduleId));


			/* Name column of trv_article_list */
			Gtk.TreeViewColumn customer_name_col = new Gtk.TreeViewColumn ();
			customer_name_col.Title = "Customer Name";
			Gtk.CellRendererText customer_name_cell = new Gtk.CellRendererText ();
			customer_name_col.PackStart (customer_name_cell, true);
			customer_name_col.SetCellDataFunc (customer_name_cell, new Gtk.TreeCellDataFunc (RenderCustomerName));

			/* Third column of trv_article_list */
			Gtk.TreeViewColumn customer_cardid_col = new Gtk.TreeViewColumn ();
			customer_cardid_col.Title = "Customer Card ID";
			Gtk.CellRendererText customer_cardid_cell = new Gtk.CellRendererText ();
			customer_cardid_col.PackStart (customer_cardid_cell, true);
			customer_cardid_col.SetCellDataFunc (customer_cardid_cell, new Gtk.TreeCellDataFunc (RenderCustomerCardid));


			trv_ticket_registered.Model = ticket_store;
			trv_ticket_registered.AppendColumn (tsche_id_col);
			trv_ticket_registered.AppendColumn (customer_name_col);
			trv_ticket_registered.AppendColumn (customer_cardid_col);
			trv_ticket_registered.CursorChanged += OnTicketChanged;

		}

		private void OnBusAvailChanged (object obj, EventArgs args)
		{
			Gtk.TreeModel model = trv_bus_avail.Model;
			Gtk.TreeIter iter;

			trv_bus_avail.Selection.GetSelected (out model, out iter);

			Schedule sch = (Schedule)model.GetValue (iter, 0);

			e_sche_id.Text = sch.ScheduleID;
		}

		private void OnTicketChanged (object obj, EventArgs args)
		{
			Gtk.TreeModel model = trv_ticket_registered.Model;
			Gtk.TreeIter iter;

			trv_ticket_registered.Selection.GetSelected (out model, out iter);

			Ticket tik = (Ticket)model.GetValue (iter, 0);
			Customer cus = get_customer (tik);

			e_sche_id.Text = tik.ScheduleID;
			e_customer_phone.Text = cus.CustomerPhone;
			e_customer_name.Text = cus.CustomerName;
			e_seats.Text = tik.Seat.ToString ();
			e_customer_idcard.Text = tik.CustomerIdcard;
			e_total.Text = tik.Total.ToString ();
		}
    
		/*  Various function to to render when selected bus changed */
		private void RenderScheId (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Schedule sch = (Schedule)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = sch.ScheduleID;
		}

		private void RenderScheDatetime (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Schedule sch = (Schedule)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = sch.Mdate;
		}

		private void RenderScheStartloc (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Schedule sch = (Schedule)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = sch.StartLoc;
		}

		private void RenderScheStoploc (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Schedule sch = (Schedule)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = sch.StopLoc;
		}

		private void RenderScheTimeconsume (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Schedule sch = (Schedule)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = sch.TimeConsume;
		}
    
		private void RenderScheSeat (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Schedule sch = (Schedule)model.GetValue (iter, 0);

			int avail_seat = available_seat (sch);
			(cell as Gtk.CellRendererText).Text = avail_seat.ToString ();
		}

		private void RenderTScheduleId (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Ticket tik = (Ticket)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = tik.ScheduleID;
		}

		private void RenderCustomerName (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Ticket tik = (Ticket)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = tik.CustomerIdcard; /* FIXME: I return the unique string not the name */
		}

		private void RenderCustomerCardid (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
		{
			Ticket tik = (Ticket)model.GetValue (iter, 0);

			(cell as Gtk.CellRendererText).Text = tik.CustomerIdcard;
		}

		protected void OnLoadDb (object sender, EventArgs e)
		{
			var schedules = from sc in db.Schedule
	select sc;

			foreach (Schedule sch in schedules) {
				schedule_store.AppendValues (sch);
			}

			var tickets = from tk in db.Ticket
	select tk;

			foreach (Ticket tik in tickets) {
				ticket_store.AppendValues (tik);

			}

		}

		protected void OnUpdateTicket (object sender, EventArgs e)
		{
			Gtk.TreeModel model = trv_ticket_registered.Model;
			Gtk.TreeIter iter;

			trv_ticket_registered.Selection.GetSelected (out model, out iter);

			Ticket tik = (Ticket)model.GetValue (iter, 0);


			foreach (Ticket t in db.Ticket) {
				if (t.TicketID == tik.TicketID) {
					t.ScheduleID = e_sche_id.Text;
					t.Customer.CustomerName = e_customer_name.Text;
					t.Customer.CustomerPhone = e_customer_phone.Text;
					t.Customer.CustomerIdcard = e_customer_idcard.Text;
					t.CustomerIdcard = e_customer_idcard.Text;
					t.Seat = Int32.Parse (e_seats.Text);
					t.Total = Int32.Parse (e_total.Text);
				}
			}
			db.SubmitChanges ();
		}

		public int delete_ticket (Ticket tik)
		{
			foreach (Ticket t in db.Ticket) {
				if (t.TicketID == tik.TicketID) {
					db.Ticket.DeleteOnSubmit (t);
				}
			}
			db.SubmitChanges ();
			return 1;
		}

		protected void OnDeleteTicket (object sender, EventArgs e)
		{
			Gtk.TreeModel model = trv_ticket_registered.Model;
			Gtk.TreeIter iter;

			trv_ticket_registered.Selection.GetSelected (out model, out iter);

			Ticket tik = (Ticket)model.GetValue (iter, 0);

			delete_ticket (tik);

			ticket_store.Remove (ref iter);
		}

		protected void OnCreateTicket (object sender, EventArgs e)
		{
			Ticket tik;
			Customer cus = new Customer ();
			cus.CustomerIdcard = e_customer_idcard.Text;
			if (!customer_exist (cus)) {
				create_customer ();
			} else
				add_point (cus);
      
			tik = new Ticket ();
			tik.ScheduleID = e_sche_id.Text;
			tik.CustomerIdcard = e_customer_idcard.Text;
			tik.Seat = Int32.Parse (e_seats.Text);
			tik.Total = Int32.Parse (e_total.Text);

			cus.CustomerPoint = customer_point (cus);
			if (customer_point (cus) >= 5) {
				tik.Total = tik.Total / 2;
				substract_point (cus);
			}

			db.Ticket.InsertOnSubmit (tik);
			db.SubmitChanges ();

			ticket_store.AppendValues (tik);
		}

		public int add_point (Customer cus)
		{
			foreach (Customer changed in db.Customer) {
				if (changed.CustomerIdcard == cus.CustomerIdcard)
					changed.CustomerPoint += 1;
			}
		
			db.SubmitChanges ();
			return 1;
		}

		public int substract_point (Customer cus)
		{
			foreach (Customer changed in db.Customer) {
				if (changed.CustomerIdcard == cus.CustomerIdcard)
					changed.CustomerPoint -= 5;
			}
		
			db.SubmitChanges ();
			return 1;
		}

		public int customer_point (Customer cus)
		{
			foreach (Customer find in db.Customer) {
				if (find.CustomerIdcard == cus.CustomerIdcard)
					return Int32.Parse (find.CustomerPoint.ToString ());
			}
			return -1;
		}

		public int available_seat (Schedule sch)
		{
			int booked = 0;
			foreach (Ticket t in db.Ticket) {
				if (t.ScheduleID == sch.ScheduleID)
					booked += Int32.Parse (t.Seat.ToString ());
			}


			foreach (Schedule s in db.Schedule) {
				if (s.ScheduleID == sch.ScheduleID)
					return Int32.Parse (s.Seats.ToString ()) - booked;
			}

			return -1;
		}
    
		public int create_customer ()
		{
			Customer cus = new Customer ();
			cus.CustomerName = e_customer_name.Text;
			cus.CustomerPhone = e_customer_phone.Text;
			cus.CustomerIdcard = e_customer_idcard.Text;
			cus.CustomerPoint = 1;

			db.Customer.InsertOnSubmit (cus);
			db.SubmitChanges ();

			return 1;
		}

		public bool customer_exist (Customer cust)
		{
			return db.Customer.Any (cus => cus.CustomerIdcard == cust.CustomerIdcard);
		}

		public int set_customer_phone (Customer cus)
		{
			foreach (Customer c in db.Customer) {
				if (c.CustomerIdcard == cus.CustomerIdcard) {
					c.CustomerPhone = cus.CustomerPhone;
				}
			}
			db.SubmitChanges ();
			return 1;
		}

		public Customer get_customer (Ticket tik)
		{
			foreach (Customer cus in db.Customer)
				if (cus.CustomerIdcard == tik.CustomerIdcard) {
					return cus;
				}
			return null;
		}


	}
}

